import pymysql

def excu(sql,param):
    con = pymysql.connect(host="localhost", user="root", password="root", database="百度员工信息")
    cursor = con.cursor()
    cursor.executemany(sql, param)
    con.commit()
    cursor.close()
    con.close()

#  针对于查询
def  find(sql,param,mode="all",size=0):
    # 1.通过四大参数获取数据库连接
    con = pymysql.connect(host="localhost", user="root", password="root", database="百度员工信息")

    # 2.通过连接创建控制台
    cursor = con.cursor()
    # 3.执行
    cursor.execute(sql,param)

    if mode =="all":
        return cursor.fetchall()
    elif mode == "one":
        return cursor.fetchone()
    else:
        return cursor.fetchmany(size)

    # 5.提交到数据
    con.commit()

    # 6.关闭资源
    cursor.close()
    con.close()

a = "SELECT COUNT(*) 人数 FROM 人员信息" #a
b ='''select count(*) from 人员信息 where 电话号码 like '%s%%' '''#b
c = 'SELECT 性别,COUNT(*) 人数 FROM 人员信息 GROUP BY 性别' #c
d = 'SELECT COUNT(*) 老员工人数 FROM 人员信息 WHERE 年龄 > 45'#d
e1 = "SELECT COUNT(*) 高薪人员数量 FROM 人员信息  WHERE 薪资 > 8000 ;" #e
e2 = "SELECT COUNT(*) 底薪人员数量 FROM 人员信息  WHERE 薪资 < 3000 ;" #e
f = '''select count(*) from 人员信息 where 外包公司  like  '%%传媒%%' '''#f
g = '''SELECT COUNT(*) 疫情 FROM 人员信息 WHERE 居住地址 LIKE '%%黑龙江%%' OR '%%北京%%' OR '%%福建%%' OR '%%四川%%' '''#g



yd = (134,135,136,137,138,139,147,150,151,152,157,158,159,178,182,183,184,187,188,1703,1705,1706)
lt = (130,131,132,145,155,156,175,176,185,189,1704,1707,1708,1709,171)
dx = (133,149,153,173,177,180,181,189,1700,1701,1702)
ydmax = 0
ltmax = 0
dxmax = 0
for i in range(len(yd)):
    data = find(b,yd[i])
    data = data[0][0]
    ydmax += data
for i in range(len(lt)):
    data = find(b,lt[i])
    data = data[0][0]
    ltmax += data
for i in range(len(dx)):
    data = find(b,dx[i])
    data = data[0][0]
    dxmax += data


a1 = find(a,[])[0][0]
print("总人数为:",a1)
b1 = ydmax
print('表格中移动用户',b1,'人','占比',round(b1/a1*100,2),'%')
b2 = ltmax
print('表格中联通用户',b2,'人','占比',round(b2/a1*100,2),'%')
b3 = dxmax
print('表格中电信用户',b3,'人','占比',round(b3/a1*100,2),'%')
c1 = find(c,[])
print("男女人数为",c1)
d1 = find(d,[])[0][0]
print("老员工人数为:",d1)
e11 = find(e1,[])[0][0]
print("高薪员工人数",e11)
e21 = find(e2,[])[0][0]
print("底薪员工人数",e21)
f1 = find(f,[])[0][0]
print("传媒公司人数:",f1)
g1 = find(g,[])[0][0]
print("高疫情地区人数:",g1)
